import json
import time
from urllib import request
from bs4 import BeautifulSoup

import mysql.connector


def initMysql():
  global mydb
  global mycursor
  # mydb = mysql.connector.connect(
  #   host='8.129.219.66',
  #   user='guifangtong',
  #   passwd='guifangtong1234!@#',
  #   database='guifangtong'
  # )

  mydb = mysql.connector.connect(
    host='localhost',
    user='root',
    passwd='root1234',
    database='guifangtong'
  )
  mycursor = mydb.cursor()


def addFangs(fang):
  initMysql()
  table_name = 't_fang'
  creat_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
  update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
  # sql = "INSERT INTO " + table_name + " (name,creat_time,update_time,deleted,des,type_id,attach) VALUES (%s, %s, %s, %s, %s, %s, %s)"
  sql = "INSERT INTO " + table_name + "(createTime,deleted,name,price,priceDetail,property,openDate,location,area,buildAddress,lngLat," \
                                     "saleAddress,traffic,hospital,market,school,special,realPics,areaPic,status,estateType," \
                                     "fixType,buildType,feature,blockCount,forSaleBlock,forSaleLayout,siteArea,floorArea," \
                                     "plannedHouseholds,parkingLotRat,greenRat,floorAreaRat,mainLayout,buildCom,estateCom,estateFee,deliverDate,content,showIndex) " \
          " VALUES (%s,%s,%s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s)"
  print(sql)
  # valsql = '(' + fang['name']+ fang['price']+ fang['priceDetail']+fang['property']+ fang['openDate']+ fang['location']+ \
  #          fang['area'], fang['buildAddress'], fang['lngLat'], fang['saleAddress'] + fang['traffic'] + fang['hospital'] + fang['market'] + fang['school'] + fang['special'] + fang['realPics'] + \
  # fang['areaPic'] + fang['status'] + fang['estateType'] + fang['fixType'] + fang['buildType'] + fang['feature'] + fang['blockCount']+\
  #          fang['forSaleBlock'] + fang['forSaleLayout'] + fang['siteArea'] + fang['floorArea'] + fang['plannedHouseholds'] + fang['parkingLotRat'] + fang['greenRat'] + fang['floorAreaRat'] + fang['mainLayout']+\
  # fang['buildCom'] + fang['estateCom'] + fang['estateFee'] + fang['deliverDate'] + fang['content'] + fang['showIndex'] + ')'
  valsql = '("'+creat_time+'",0,"' + fang['name']+'","'+ fang['price']+'","'+ fang['priceDetail']+'","'+fang['property']+'","'+ fang['openDate']+'","'+ fang['location']+'",'+\
           fang['area']+',"'+fang['buildAddress']+'","'+ fang['lngLat']+'","'+ fang['saleAddress'] +'","'+ fang['traffic'] +'","'+ fang['hospital'] +'","'+ \
           fang['market'] +'","'+ fang['school'] +'","'+ fang['special'] +'","'+ fang['realPics'] +'","'+ fang['areaPic'] +'","'+ fang['status'] +'","'+ \
           fang['estateType'] +'",'+ fang['fixType'] +','+ fang['buildType'] +',"'+ fang['feature'] +'","'+ fang['blockCount']+'","'+ fang['forSaleBlock'] +'","'+ \
           fang['forSaleLayout'] +'","'+ fang['siteArea'] +'","'+ fang['floorArea'] +'","'+ fang['plannedHouseholds'] +'","'+ fang['parkingLotRat'] +'","'+ \
           fang['greenRat'] +'","'+ fang['floorAreaRat'] +'","'+ fang['mainLayout']+'","'+ fang['buildCom'] +'","'+ fang['estateCom'] +'","'+ fang['estateFee'] +'","'+ \
           fang['deliverDate'] +'","'+ fang['content'] +'",'+ fang['showIndex'] +')'
  print(valsql)
  mycursor.execute(sql, valsql)
  mydb.commit()  # 数据表内容有更新，必须使用到该语句


def sousuoLgb():
  url = 'https://lgbao.logan.com.cn/olshow/v1/web/product/estate/query/recommend?city=450100&province=450000&recommendData=0'
  # 直接请求
  response = request.urlopen(url)
  # 获取状态码，如果是200表示成功
  # print(response.status)
  # 读取网页内容
  context = response.read().decode('utf-8')
  data = json.loads(context)
  # print(data['data'])
  for item in data['data']:
    fangObj = {
      # 第一排
      'name': item['estateName'],
      'price': str(item['averagePriceStart']),
      'priceDetail': '均价'+str(item['averagePriceStart'])+'-'+str(item['averagePriceEnd'])+'（毛坯）',
      'property': str(item['propertyRight']),
      'openDate':  str(item['recentOpening']),
      'location':  str(item['address']),
      'area': '1',
      'buildAddress': str(item['mapAddress']),
      'lngLat': str(item['latitude']) + '","' +str(item['longitude']),
      'saleAddress':  str(item['saleMapAddress']),

      # 第二排
      'traffic': ' 公交：15、26、56、702路，五象小学站，49、107路，龙堤五象路口站，12路，五象大桥南站；地铁：地铁4号线玉象站（在建）',
      'hospital': ' 广西国际壮医医院、江滨医院、工人医院、医科大一附院；',
      'market': ' 五象航洋城、梦之岛、威宁邻家广场、宜家；',
      'school': ' 幼儿园：大观天下幼儿园，多元新兴实验幼儿园，良庆区第三实验幼儿园；中小学：五象小学，龙堤中学；大学：广西医科大学',
      'special': '11,1,2',
      'realPics': 'group1/M00/00/00/rBOXs1-qZGSADnn7AA2hdWnaXM0418.png|group1/M00/00/00/rBOXs1-qZGSAf5uXAA2EG2Suxr8397.png|group1/M00/00/00/rBOXs1-qZGSAZoq-ABNzPKl0Kbc236.png',
      'areaPic': 'group1/M00/00/00/rBOXs1-qZGqAaf4KAA5TS7yLQA4839.png',
      'status':  str(item['salesStatus']),
      'estateType':  str(2),
      'fixType':  str(2),
      'buildType':  str(3),
      'feature': '',
      'blockCount':  str(item['plotRatio']),

      # 第三排
      'forSaleBlock':  str(item['houseHolds']),
      'forSaleLayout':  str(int(item['plotRatio'])*8),
      'siteArea':  str(item['coverArea']),
      'floorArea': str(item['saleableArea'])+'㎡',
      'plannedHouseholds':  str(int(item['plotRatio'])/4),
      'parkingLotRat': str( int(item['plotRatio']) * 5),
      'greenRat': str( item['greenRatio']),
      'floorAreaRat': '3.254',
      'mainLayout':  str(int(item['plotRatio']) * 4),

      # 第四排
      'buildCom':item['developers'],
      'estateCom':item['propertyCompany'],
      'estateFee': str(item['propertyFee']),
      'deliverDate': str(item['planDeliveryDate']),
      'content':item['hightlightSummary'],
      'showIndex': str(item['shelvesStatus'])
    }
    addFangs(fangObj)
    # print(fangObj)
    # print(item['estateName'])


if __name__ == '__main__':
  sousuoLgb()
  # addFangs()
  # addFangs()
